﻿ INSERT [dbo].[DicItem]([DicTypeID],[DicItemValue],[DicItemDesc],[IsEnabled])
     VALUES(3,'取消','取消',1)
Go

delete  from cat where catid in (7,8)
delete FROM CATAssignment where catid in (7,8)
GO
INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (57
           ,1)
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (57
           ,2)
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (57
           ,3)
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (58
           ,1)
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (58
           ,2)
    INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (58
           ,3)



           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (59
           ,1)
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (59
           ,2)
    INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (59
           ,3)
 
 
    INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (60
           ,1)
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (60
           ,2)
    INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (60
           ,3)
   
GO


INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (61
           ,1)
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (61
           ,2)
    INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (61
           ,3)


INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (62
           ,1)
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (62
           ,2)
    INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (62
           ,3)
           
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (63
           ,1)
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (63
           ,2)
    INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (63
           ,3)
           
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (64
           ,1)
           INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (64
           ,2)
    INSERT INTO [PreContract].[dbo].[MenuRoleMapping]
           ([MenuID]
           ,[RoleID])
     VALUES
           (64
           ,3)

Go
/****** Object:  StoredProcedure [dbo].[sp_GetReportByCatStatusNo]    Script Date: 11/25/2009 16:30:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[sp_GetReportByCatStatusNo]
@currentDate datetime=null,
@currentType int =0
as
SET NOCOUNT ON;
if @currentDate is null
begin
	set @currentDate = getdate()
end
DECLARE @DtToday SMALLDATETIME
DECLARE @ExRate DECIMAL(8,6)
--GET Exchange Rate
SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC
	SELECT a.CATID,'<1Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			----AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31<1
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'1-3Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=1 AND DateDiff(day,ERPReceived,@currentDate)/31<3
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'3-6Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=3 AND DateDiff(day,ERPReceived,@currentDate)/31<6
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'6-12Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=6 AND DateDiff(day,ERPReceived,@currentDate)/31<12
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'>1Year' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=12
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'0-19%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent>=0 AND a.DeliveryPercent<20
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'20-99%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent>=20 AND a.DeliveryPercent<100
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'100%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent=100
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'Total' AS DeliveryType,
		ISNULL(ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0),0) AS Amount
	FROM dbo.PreContractInfo a 
		INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1
			AND (@currentType=0 OR TypeID=@currentType) 
		GROUP BY a.CATID	
SET NOCOUNT OFF;

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByCatStatusYes]    Script Date: 11/25/2009 16:30:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[sp_GetReportByCatStatusYes]
@year int=0,
@currentType int =0
as
SET NOCOUNT ON;
DECLARE @DtToday SMALLDATETIME
DECLARE @ExRate DECIMAL(8,6)
--GET Exchange Rate
SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC
	SELECT a.CATID,'<1Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31<1
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'1-3Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=1 AND DateDiff(day,ERPReceived,SignActual)/31<3
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'3-6Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=3 AND DateDiff(day,ERPReceived,SignActual)/31<6
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'6-12Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=6 AND DateDiff(day,ERPReceived,SignActual)/31<12
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'>1Year' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=12
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.CATID
	UNION ALL
	SELECT a.CATID,'Total' AS DeliveryType,
		ISNULL(ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
		INNER JOIN CAT b ON a.CATID=b.CATID
		WHERE a.StatusID=12 
			--AND b.Active=1
			AND (@currentType=0 OR TypeID=@currentType) 
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.CATID	
SET NOCOUNT OFF;

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvByCatStatusNo]    Script Date: 11/25/2009 16:30:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[sp_GetReportByProvByCatStatusNo]
@currentDate datetime=null,
@currentType int =0
as
SET NOCOUNT ON;
if @currentDate is null
begin
	set @currentDate = getdate()
end
DECLARE @DtToday SMALLDATETIME
DECLARE @ExRate DECIMAL(8,6)
--GET Exchange Rate
SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC
	SELECT a.ProvinceID,a.CatId,'<1Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31<1
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID,a.CatId
	UNION ALL
	SELECT a.ProvinceID,a.CatId,'1-3Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=1 AND DateDiff(day,ERPReceived,@currentDate)/31<3
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID,a.CatId
	UNION ALL
	SELECT a.ProvinceID,a.CatId,'3-6Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=3 AND DateDiff(day,ERPReceived,@currentDate)/31<6
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID,a.CatId
	UNION ALL
	SELECT a.ProvinceID,a.CatId,'6-12Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=6 AND DateDiff(day,ERPReceived,@currentDate)/31<12
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID,a.CatId
	UNION ALL
	SELECT a.ProvinceID,a.CatId,'>1Year' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=12
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID,a.CatId
	UNION ALL
	SELECT a.ProvinceID,a.CatId,'0-19%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent>=0 AND a.DeliveryPercent<20
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID,a.CatId
	UNION ALL
	SELECT a.ProvinceID,a.CatId,'20-99%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent>=20 AND a.DeliveryPercent<100
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID,a.CatId
	UNION ALL
	SELECT a.ProvinceID,a.CatId,'100%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent=100
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID,a.CatId
	UNION ALL
	SELECT a.ProvinceID,a.CatId,'Total' AS DeliveryType,
		ISNULL(ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0),0) AS Amount
	FROM dbo.PreContractInfo a 
		INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID,a.CatId	
SET NOCOUNT OFF;

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvStatusNo]    Script Date: 11/25/2009 16:30:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[sp_GetReportByProvStatusNo]
@currentDate datetime=null,
@currentType int =null
as
SET NOCOUNT ON;
if @currentDate is null
begin
	set @currentDate = getdate()
end
DECLARE @DtToday SMALLDATETIME
DECLARE @ExRate DECIMAL(8,6)
--GET Exchange Rate
SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC
	SELECT a.ProvinceID,'<1Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31<1
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'1-3Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=1 AND DateDiff(day,ERPReceived,@currentDate)/31<3
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'3-6Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=3 AND DateDiff(day,ERPReceived,@currentDate)/31<6
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'6-12Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=6 AND DateDiff(day,ERPReceived,@currentDate)/31<12
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'>1Year' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=12
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'0-19%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent>=0 AND a.DeliveryPercent<20
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'20-99%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent>=20 AND a.DeliveryPercent<100
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'100%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent=100
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'Total' AS DeliveryType,
		ISNULL(ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0),0) AS Amount
	FROM dbo.PreContractInfo a 
		INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.ProvinceID	
SET NOCOUNT OFF;

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByProvStatusYes]    Script Date: 11/25/2009 16:30:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[sp_GetReportByProvStatusYes]
@year int=0,
@currentType int =0
as
SET NOCOUNT ON;
DECLARE @DtToday SMALLDATETIME
DECLARE @ExRate DECIMAL(8,6)
--GET Exchange Rate
SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC
	SELECT a.ProvinceID,'<1Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31<1
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'1-3Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=1 AND DateDiff(day,ERPReceived,SignActual)/31<3
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'3-6Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=3 AND DateDiff(day,ERPReceived,SignActual)/31<6
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'6-12Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=6 AND DateDiff(day,ERPReceived,SignActual)/31<12
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'>1Year' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=12
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.ProvinceID
	UNION ALL
	SELECT a.ProvinceID,'Total' AS DeliveryType,
		ISNULL(ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
		INNER JOIN Province b ON a.ProvinceID=b.ProvinceID
		WHERE a.StatusID=12 
			--AND b.Active=1
			AND (@currentType=0 OR TypeID=@currentType) 
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.ProvinceID	
SET NOCOUNT OFF;

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByPuStatusNo]    Script Date: 11/25/2009 16:30:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[sp_GetReportByPuStatusNo]
@currentDate datetime=null,
@currentType int =null
as
SET NOCOUNT ON;
if @currentDate is null
begin
	set @currentDate = getdate()
end
DECLARE @DtToday SMALLDATETIME
DECLARE @ExRate DECIMAL(8,6)
--GET Exchange Rate
SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC
	SELECT a.BDID,'<1Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31<1
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'1-3Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=1 AND DateDiff(day,ERPReceived,@currentDate)/31<3
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'3-6Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=3 AND DateDiff(day,ERPReceived,@currentDate)/31<6
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'6-12Month' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=6 AND DateDiff(day,ERPReceived,@currentDate)/31<12
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'>1Year' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,@currentDate)/31>=12
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'0-19%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent>=0 AND a.DeliveryPercent<20
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'20-99%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent>=20 AND a.DeliveryPercent<100
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'100%<br>Delivered' AS DeliveryType,
		ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0) AS Amount
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND a.DeliveryPercent=100
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'Total' AS DeliveryType,
		ISNULL(ISNULL(SUM(ROUND(OrionPrice /@ExRate / 1170000, 4)),0),0) AS Amount
	FROM dbo.PreContractInfo a 
		INNER JOIN BD b ON a.BDID=b.BDID
		WHERE (a.StatusID=10 OR a.StatusID=11) 
			--AND b.Active=1 
			AND (@currentType=0 OR TypeID=@currentType)
		GROUP BY a.BDID	
SET NOCOUNT OFF;

GO

/****** Object:  StoredProcedure [dbo].[sp_GetReportByPuStatusYes]    Script Date: 11/25/2009 16:30:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Procedure [dbo].[sp_GetReportByPuStatusYes]
@year int=2009,
@currentType int =0
as
SET NOCOUNT ON;
if @year=0
return
DECLARE @DtToday SMALLDATETIME
DECLARE @ExRate DECIMAL(8,6)
--GET Exchange Rate
SELECT TOP 1 @ExRate=[Rate] FROM dbo.ExchangeRate ORDER BY UpdateDate DESC
	SELECT a.BDID,'<1Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31<1
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'1-3Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=1 AND DateDiff(day,ERPReceived,SignActual)/31<3
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'3-6Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=3 AND DateDiff(day,ERPReceived,SignActual)/31<6
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'6-12Month' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=6 AND DateDiff(day,ERPReceived,SignActual)/31<12
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'>1Year' AS DeliveryType,
		ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
	INNER JOIN BD b ON a.BDID=b.BDID
		WHERE a.StatusID=12 
			--AND b.Active=1 
			AND DateDiff(day,ERPReceived,SignActual)/31>=12
			AND (@currentType=0 OR TypeID=@currentType)
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.BDID
	UNION ALL
	SELECT a.BDID,'Total' AS DeliveryType,
		ISNULL(ISNULL(SUM(ROUND(CEWPromised /@ExRate / 1170000, 4)),0),0) AS Amount,Count(*) as Num
	FROM dbo.PreContractInfo a 
		INNER JOIN BD b ON a.BDID=b.BDID
		WHERE a.StatusID=12 
			--AND b.Active=1
			AND (@currentType=0 OR TypeID=@currentType) 
			AND (@year=0 OR year(SignActual)=@year)
		GROUP BY a.BDID	
SET NOCOUNT OFF;

GO


